﻿using System;
using System.Collections.Generic;
using System.Data.SqlServerCe;
using NyMPH.Patches;
using NyMPH.Persistence.DSNyMPHTableAdapters;
using Logging;
using System.Diagnostics;
using System.Linq;

namespace NyMPH.Persistence
{
    /// <summary>
    /// Provides database support for the Patch class to SQL-based database (currently SQL CE 3.5)
    /// </summary>
    public class PatchesDataHelper
    {
        private SqlCeConnection _conn;

        //Create a file log for errors just for debugging, otherwise write to event log
#if DEBUG
        private static EventLogHelper _elh = new EventLogHelper("NyMPH.Persistence.Database.Errors.log");

#else
        private EventLogHelper _elh = new EventLogHelper("NyMPH", "Database");
         
#endif

        #region Constructors
        /// <summary>
        /// Creates an instance of PatchDataHelper. This requires a valid Connection object.
        /// </summary>
        /// <param name="Connection">Any connection object that inherits from DbConnection</param>
        /// <exception cref="NullReferenceException">An exception will be thrown if the DbConnection object is null.</exception>
        public PatchesDataHelper(SqlCeConnection Connection)
        {
            if (Connection != null)
            {
                //set data objects
                _conn = Connection;
                //TODO: Needed?
            }
            else
            {
                throw new NullReferenceException("Connection object cannot be null.");
            }
        }
        
        /// <summary>
        /// Creates an instance of KeywordDataHelper. No connection object is specified.
        /// </summary>

        public PatchesDataHelper()
        {

        }

        #endregion

        #region Persistence Methods

        /// <summary>
        /// Retrieve the PatchOID of a Patch object based on its Name
        /// </summary>
        /// <param name="_Patch">An instance of Patch</param>
        /// <returns>The PatchOID value of the given Patch (will return -1 if it is not found)</returns>
        public int FindPatchOID(Patch _Patch)
        {
            string _sql = "SELECT PatchOID FROM tbPatch " +
                          "WHERE PatchName = @Name";

            int _oid = -1;

            SqlCeCommand _cmd = new SqlCeCommand(_sql, _conn);
            _cmd.Parameters.AddWithValue("@Name", _Patch.Name);


            try
            {
                _cmd.Connection.Open();

                _oid = (int)_cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw new NotImplementedException("FindPatchOID threw an exception.", ex);
            }

            finally
            {
                _cmd.Connection.Close();
                _cmd.Dispose();
            }
            return _oid;
        }

        /// <summary>
        /// Persists an instance of Patch to the database.
        /// </summary>
        /// <param name="Item">A Patch instance to add or update. If added, this Patch will have its OID property assigned.</param>
        /// <returns>The number of rows affected; this will be -1 if an exception occurs.</returns>
        public int SavePatch(ref Patch Item)
        {
            //TODO: update related records.
            //TODO: Use DataSet object for related records.
            int _result = 0;

            if (Item != null)
            {
                //create typed data objects
                DSNyMPH.tbPatchesDataTable _dtPatch = new DSNyMPH.tbPatchesDataTable();

                try
                {
                    using (tbPatchesTableAdapter _taPatches = new tbPatchesTableAdapter())
                    {
                        //set connection object
                        _taPatches.Connection = _conn;

                        //fill typed table with instance(s) of this Patch
                        _taPatches.FillByOID(_dtPatch, Item.OID);

                        //If no records are returned, add this Keyword to the datatable.
                        if (_dtPatch.Count == 0)
                        {
                            DSNyMPH.tbPatchesRow _row = _dtPatch.NewtbPatchesRow();

                            //set properties
                            MapPatchToRow(Item, ref _row);

                            _dtPatch.AddtbPatchesRow(_row);
                            //update changes to datatable using table adapter
                            _result = _taPatches.Update(_dtPatch);

                            //update PatchOID
                            Item.OID = FindPatchOID(Item);
                        }
                        else
                        {
                            //should only be one instance
                            DSNyMPH.tbPatchesRow _row = (DSNyMPH.tbPatchesRow)_dtPatch.Rows[0];

                            MapPatchToRow(Item, ref _row);

                            //update changes to datatable using table adapter
                            _result = _taPatches.Update(_dtPatch);
                        }
                    }
                }
                catch (Exception ex)
                {
                    //write to logging
                    _elh.WriteEvent(string.Format("Exception occurred while saving Keyword   {0}. Exception details: {1}", Item.ToString(), ex.Message), EventLogEntryType.Error);

                    _result = -1;
                }
            }
            else
            {
                _result = -1;
            }
            return _result;
        }

        /// <summary>
        /// Deletes the specified Patch from the database. 
        /// </summary>
        /// <param name="Item">The Patch to delete.</param>
        /// <returns>The number of records affected. This will be -1 if an exception occurs.</returns>
        public int DeletePatch(Patch Item)
        {
            //TODO: Cleanup orphaned records.
            int _result = 0;

            //verify not null and OID isn't null
            if ((Item != null) && (Item.OID != -1))
            {
                //create typed data objects
                DSNyMPH.tbPatchesDataTable _dtPatch = new DSNyMPH.tbPatchesDataTable();

                try
                {
                    using (tbPatchesTableAdapter _taPatch = new tbPatchesTableAdapter())
                    {
                        //fill typed table with instance(s) of this Patch
                        _taPatch.FillByOID(_dtPatch, Item.OID);

                        //If no records are returned, delete this Patch from the datatable.
                        if (_dtPatch.Count == 1)
                        {
                            //should only be one instance
                            _dtPatch.Rows[0].Delete();
                        }
                        else
                        {
                            //TODO: Shouldn't be multiple rows, but handle it better
                            throw new NotImplementedException("Handle multiple Patches");
                        }
                        // update deletion changes to datatable using table adapter
                        _result = _taPatch.Update(_dtPatch);
                    }
                }
                catch (Exception ex)
                {
                    //write to logging
                    _elh.WriteEvent(string.Format("Exception occurred while deleting Patch   {0}. Exception details: {1}", Item.ToString(), ex.Message), EventLogEntryType.Error);

                    _result = -1;
                }
            }
            else
            {
                _result = -1;
            }

            return _result;

        }

        //todo: return new Keyword?
        public Patch LoadPatch(int PatchOID)
        {
            //create typed data objects
            DSNyMPH.tbPatchesDataTable _dtPatch = new DSNyMPH.tbPatchesDataTable();

            try
            {
                using (tbPatchesTableAdapter _taPatch = new tbPatchesTableAdapter())
                {

                    _taPatch.Connection = _conn;
                    //fill typed table
                    _taPatch.FillByOID(_dtPatch, PatchOID);
                }
            }
            catch (Exception ex)
            {
                _elh.WriteEvent(string.Format("Exception occurred while getting Keywords with KeywordOID =  {0}. Exception details: {1}", PatchOID.ToString(), ex.Message), EventLogEntryType.Error);
            }

            if (_dtPatch.Rows.Count == 0)
            {
                return new Patch();
            }
            else
            {
                //map row to object
                return MapRowToPatch(_dtPatch.Rows[0]);
            }
        }


        /// <summary>
        /// Converts an instance of tbPatchesRow to a 'real' Keyword object.
        /// </summary>
        /// <param name="PatchRow">A single instance of tbPatchesRow to parse into a Patch object.</param>
        /// <returns>A new Patch object with properties set to the values in the row.</returns>
        public static Patch MapRowToPatch(System.Data.DataRow PatchRow)
        {
            Patch _new = new Patch();

            //check for null row, then map fields to object
            if (PatchRow != null)
            {
                //call data mapper
                DataMapping.Map(PatchRow, _new, true);
            }

            return _new;
        }

        /// <summary>
        /// Maps an instance of Patch to a typed DataRow
        /// </summary>
        /// <param name="_Patch">An instance of Patch to create a typed DataRow for.</param>
        /// <param name="_Row">A referenced typed DataRow to assign values to.</param>
        public static void MapPatchToRow(Patch _Patch, ref DSNyMPH.tbPatchesRow _Row)
        {
            //map object to typed row.
            if (_Row != null)
            {
                _Row.PatchName = _Patch.Name;
                _Row.OriginalLocation = _Patch.OriginalLocation;
                _Row.Rating = _Patch.Rating;
                _Row.LastModified = _Patch.LastModified;
                _Row.IsNew = _Patch.IsNew;
                _Row.IsArchived = _Patch.IsArchived;
                _Row.Description = _Patch.Description;
                _Row.Usage = _Patch.Usage;
                _Row.WhenCreated = _Patch.WhenCreated;
                _Row.PatchName = _Patch.Name;

                //TODO: Handle relationships with other objects
            }
        }

        #endregion
    }
}
